package com.controller;

import com.Model.StudentModel;
import com.Repository.StudentRepository;
import com.entity.Student;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.*;
import org.springframework.data.jpa.domain.Specification;
import org.springframework.web.bind.annotation.*;

import javax.persistence.criteria.CriteriaBuilder;
import javax.persistence.criteria.CriteriaQuery;
import javax.persistence.criteria.Predicate;
import javax.persistence.criteria.Root;
import java.util.ArrayList;
import java.util.List;


@RestController
@RequestMapping("/student")
public class StudentHandle {

    @Autowired
    private StudentRepository studentRepository;


//增删改
    @PostMapping("/save")
    public Student save(@RequestBody Student s1){
        return studentRepository.save(s1);
    }

    @PutMapping("/put")
    public Student updata(@RequestBody Student s1){
        return studentRepository.save(s1);
    }

    @DeleteMapping("/delete/{id}")
    public String deleteById(@PathVariable("id") Long id){
        studentRepository.deleteById(id);
        return "delete success";
    }



//查1. 继承接口JpaRepository：主要方法findAll
    @GetMapping("/findAll")
    public List<Student> findAll(){
        return studentRepository.findAll();
    }
    //分页查询
    @GetMapping("/findByIdPage")
    public Page<Student> findByIdPage(){
        Pageable pageable = PageRequest.of(0,4);
        return studentRepository.findAll(pageable);
    }
    //排序
    @GetMapping("/findAllSort")
    public List<Student> findAllSort(){
        Sort sort = new Sort(Sort.Direction.ASC, "score");
        return studentRepository.findAll(sort);
    }
    //分页查询
    @GetMapping("/findByIdPageSort")
    public Page<Student> findByIdPageSort(){
        Sort sort = new Sort(Sort.Direction.DESC, "score");
        Pageable pageable = PageRequest.of(0,4, sort);
        return studentRepository.findAll(pageable);
    }
    //分页查询：page与list间的类型转换
    @GetMapping("/findByIdPageSort1")
    public List<Student> findByIdPageSort1(){
        Sort sort = new Sort(Sort.Direction.DESC, "score");
        Pageable pageable = PageRequest.of(0,4, sort);
        Page<Student> pages = studentRepository.findAll(pageable);
        List<Student> students =  pages.getContent();
        return students;
    }
    //findAllById
    @GetMapping("/findAllById")
    private List<Student> findAllById(){
        List<Long> ids = new ArrayList<Long>();
        ids.add(4L);
        ids.add(5L);
        return studentRepository.findAllById(ids);
    }
    //求数目
    @GetMapping("/AllCount")
    public long AllCount(){
        return studentRepository.count();
    }




//查2. 自定义函数接口： Or And In Like Between等
    @GetMapping("/findbyId/{id}")
    public List<Student> findbyId(@PathVariable("id") Long id){
        return studentRepository.getById(id);
    }
    @GetMapping("/findByAge")
    public List<Student> findByAge(){
        return studentRepository.getByAge(76);
    }
    @GetMapping("/findByName")
    public List<Student> findByName(){
        return studentRepository.getByName("sp1");
    }
    //Or
    @GetMapping("/findByNameOrScore/{name}/{score}")
    public List<Student> findByNameOrScore(
            @PathVariable("name") String name,
            @PathVariable("score") Double score){
        return studentRepository.getByNameOrScore(name, score);
    }
    //And
    @GetMapping("/findByNameAndScore")
    public Student findByNameAndScore(){
        return studentRepository.getByNameAndScore("sp1", 50D);
    }
    //like
    @GetMapping("/findByNameLike")
    private List<Student> findByNameLike(){
        return studentRepository.findByNameLike("%un%");
    }
    //<
    @GetMapping("/findByScoreLessThan")
    public List<Student> findByScoreLessThan(){
        return studentRepository.findByScoreLessThan(80D);
    }
    //Between

    @GetMapping("/findByAgeBetween")
    public List<Student> findByAgeBetween(){
        return studentRepository.findByAgeBetween(20, 100);
    }


//查3. jpal查询
    @GetMapping("/findByCondition2")
    List<Student> findByCondition2(String name, Integer age){
        return  studentRepository.findByCondition2("sp1", 73);
    }
    @GetMapping("/findByCondition3")
    List<Student> findByCondition3(){
        return studentRepository.findByCondition3("sp1", 73);
    }
    @GetMapping("/findByCondition4")
    List<Student> findByCondition4(){
        Student student = new Student();
        student.setName("sp1");
        student.setAge(73);
        return studentRepository.findByCondition4(student);
    }



//查4. mysql查询
    @GetMapping("/findByMysql")
    public List<Student> findByMysql(String name, Integer age){    //必须要有public，否则就只是本包作用域，就无法和外卖呢的mysql对接
        return  studentRepository.findByMysql("sp2", 73);
    }



//5. Specifications动态查询   //问3：还有很多内容
    @GetMapping("/FindSpecification")
    public List<Student> FindSpecification(){

        final String name = null;
        final Integer age = 0;

        //root: 实体对象，可以通过它获取属性值
        //criteriaQuery：用于生成sql语句
        //criteriaBuilder：用于拼接查询条件
        List<Student> students = studentRepository.findAll(new Specification<Student>() {
            public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<Predicate>();
                if(name != null){
                    predicates.add(criteriaBuilder.equal(root.get("name"), name));
                }
                if(age != 0){
                    predicates.add(criteriaBuilder.equal(root.get("age"), age));
                }
                return criteriaBuilder.and(predicates.toArray(new Predicate[]{})); //and全部 , or为空
                //criteriaQuery用法：return criteriaQuery.where(predicates.toArray(new Predicate[predicates.size()])).getRestriction();
            }
        });
        return students;
    }

    @GetMapping("/FindSpecificationPage")
    public Page<Student> FindSpecificationPage(){

        final String name = null;
        final Integer age = 0;
        Pageable pageable = PageRequest.of(0, 4);

        Page<Student> students = studentRepository.findAll(new Specification<Student>() {

            public Predicate toPredicate(Root<Student> root, CriteriaQuery<?> criteriaQuery, CriteriaBuilder criteriaBuilder) {
                List<Predicate> predicates = new ArrayList<Predicate>();
                if(name != null){
                    predicates.add(criteriaBuilder.equal(root.get("name"), name));
                }
                if(age != 0){
                    predicates.add(criteriaBuilder.equal(root.get("age"), age));
                }
                return criteriaBuilder.and(predicates.toArray(new Predicate[]{})); //and全部 , or为空
            }
        }, pageable);

        return students;
    }



//6. Example动态查询
    public void findByExample1(){
        Student student = new Student();
        student.setId(1L);
        //studentRepository.findAll(Example.of(student));  //问4：Example.of报错
    }



//params、@RequestBody、Mapping等的用法
    @GetMapping("/findParams")
    public List<Student> findParams(Long id, String name){
        return studentRepository.findAll();
    }

    @GetMapping("/findBody")
    public List<Student> findBody(@RequestBody StudentModel studentModel){
        return studentRepository.findAll();
    }

    @PostMapping("/saveBody")
    public Student saveBody(@RequestBody Student s1){
        return studentRepository.save(s1);
    }

    @PostMapping("/saveObject")
    public String saveObject(Student s1, Long id){
        return s1.toString()+id;
    }

    @PostMapping("/saveParams")
    public Student saveParams(Long id, String name){
        Student s1 = new Student();
        s1.setId(id);
        s1.setName(name);
        return studentRepository.save(s1);
    }

}

/*
查询总结：
1.使用父接口
    1.1 findAllById 根据主键查
    1.2 findAll() 重载，可以排序，分页
2.方法命名规则
    例2.1：findByName("sunpeng") ，系统解析：
        1.去掉findBy，
        2.将剩余的 Name("sunpeng") 与 select * from student where 拼接
        3. 拼接的结果为select * from student where Name = sunpeng;
    例2.2：findByNameLike("%un%"),系统解析为select * from student where Name like un;
    注2.1：findByIdIn(id1,id2)、findByIdIn(List<id>)和findAllById(List<id>)的用途一样
    注2.2：OrderBy 与 findAll(sort)一样
3.jpql查询
    它是jpa定义的语言，用来统一不同sql语句之间的差异，使用起来和mysql一模一样，只需把表名换成类名，表的字段换成类的字段
    3.1 占位从1开始：？1
        如：@Query("from Student s where  s.name = ?1 or s.age = ?2")
    3.2 :name 和 @Param("name") 名字必须一样，和String untitled1可以不一样
        @Query("from Student  where  name = :name or age = :age")
        List<Student> findByCondition3(@Param("name") String untitled1, @Param("age") Integer untitled2);
    3.3 传入对象Bean，那么jpql的写法为：固定写法，没有为什么
        @Query("from Student s where  s.name = :#{#student.name} or s.age = :#{#student.age}")
        List<Student> findByCondition4(@Param("student") Student student);
4.本地sql查询
    @Query中写原生的sql语句，这样的话也就是针对单一的数据库了
5.Specifications动态查询
 */
